package cn.explink.dop.dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import cn.explink.dop.dto.ReturnSettleDTO;
import cn.explink.dop.entity.vo.OrderVO;
import cn.explink.dop.entity.vo.ReturnRecordVO;
import cn.explink.dop.entity.vo.ReturnVO;
import cn.explink.dop.entity.vo.SettleAccoutsRecordVO;
import cn.explink.dop.enums.OrderStatusEnum;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;
import cn.explink.eap.core.util.ResourceUtil;
import cn.explink.eap.web.system.pojo.base.TSUser;

/*
 * @author  wangzy
 */
@Repository("returnDao")
public class ReturnDao extends GenericBaseCommonDao<ReturnVO, String> {

	/*
	 * heibrnate方式 创建归班单 插入操作
	 */
	public String creatReturn(ReturnVO returnVO) {
		String id = (String) this.getSession().save(returnVO);
		// this.batchSave(list);
		return id;
	}

	/*
	 * jdbc方式 创建归班单 插入操作
	 *
	 * public String creatReturn(List<ReturnVO> list) { this.batchSave(list);
	 * String key = returnList[0]. // final String sql =
	 * "insert into or_return (send_personname,send_time,return_billno,order_count,up_money,return_status) values (?,?,?,?,?,?)"
	 * ; Map<String, Long> returnBillIdMap = new HashMap<String, Long>();
	 * KeyHolder keyHolder = new GeneratedKeyHolder(); for (final ReturnVO
	 * returnVO : list) { super.getJdbcTemplate().update(new
	 * PreparedStatementCreator() { public PreparedStatement
	 * createPreparedStatement( // Connection connection) throws SQLException {
	 * PreparedStatement ps = connection.prepareStatement( // sql.toString(),
	 * new String[] { "id" }); ps.setString(1, returnVO.getSendPersonName());
	 * ps.setString(2, returnVO.getCreateTimeStr()); // ps.setString(3,
	 * returnVO.getBillNo()); // ps.setInt(4, returnVO.getNumber()); //
	 * ps.setFloat(5, returnVO.getMoney()); // //
	 * Int(5,Integer.parseInt(returnVO.getMoney())); // ps.setString(6,
	 * returnVO.getStatus()); // return ps; // } // }, keyHolder); //
	 * returnVO.setId(keyHolder.getKey().toString()); // // 逻辑有问题 // // //
	 * returnBillIdMap.put(returnVO.getOrgId(),keyHolder.getKey().longValue());
	 * // // } // //String key = ""; // return keyHolder.getKey().toString();
	 * return null; }
	 */

	/*
	 * 更新订单表 标示更新的订单属于哪个归班单
	 */
	public String updateOrderByReturnPrimaryKey(String key) {
		String sql = "UPDATE   or_order SET  return_id = 32552 WHERE deliveryman_id =   (SELECT     bd_deliveryman.id   FROM    bd_deliveryman,    or_return   WHERE bd_deliveryman.del_name = or_return.send_personname    AND del_name = '张成'   GROUP BY del_name)   AND or_order.create_date LIKE '%2015-03-25%' ";

		int a = super.getJdbcTemplate().update(sql);
		if (a > 0) {
			return "succes";
		}
		return "error";
	}

	/*
	 * 更新归班单状态 已交款，未交款状态
	 */
	public int updateReturnStatus(String billno) {
		String sql = "UPDATE or_return SET return_status='已上交' WHERE return_billno in (" + billno + ")";
		int count = super.getJdbcTemplate().update(sql);
		return count;
	}

	/*
	 * 删除归班单
	 */
	public void deleteReturn(String billno) {
		String sql = "DELETE FROM or_return WHERE return_billno = '" + billno + "'";
		super.getJdbcTemplate().update(sql);
	}

	/*
	 * 删除订单
	 */
	public int deleteOrder(String returnid) {
		// String sql = "DELETE FROM or_order WHERE return_id = '" + returnid
		// + "'";
		String updateSql = "UPDATE or_order SET return_id = NULL WHERE return_id = '" + returnid + "'";
		int count = super.getJdbcTemplate().update(updateSql);
		return count;
	}

	/*
	 * 查询归班表————归班结算页面————生成归班单包含的查询
	 */
	public Map<String, Object> queryReturn(final ReturnVO returnVO, final int pageNum, final int pageSize) {

		StringBuffer allFromSql = new StringBuffer("from ReturnVO ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from ReturnVO ");
		StringBuffer whereSql = this.getWhereSql(returnVO);
		// if(0 != sto.getRestaurantId()){
		// sql.append(" and restaurant_id=").append(sto.getRestaurantId());
		// }

		Map<String, Object> map = new HashMap<String, Object>();
		Query queryCount = this.getSession().createQuery(this.processWhere(countFromSql.append(whereSql).toString()));
		Integer count = ((Long) queryCount.uniqueResult()).intValue();

		map.put("count", count);

		String sql = this.processWhere(allFromSql.append(whereSql).toString());

		List<OrderVO> orderList = this.getSession().createQuery(sql).setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list();

		map.put("list", orderList);
		return map;
	}

	/*
	 * 查询归班表————归班结算页面————查询按钮
	 */
	public Map<String, Object> queryReturnForSettle(final ReturnVO returnVO, final int pageNum, final int pageSize) {

		StringBuffer allFromSql = new StringBuffer("from ReturnVO ");
		StringBuffer countFromSql = new StringBuffer("SELECT COUNT(id) FROM or_return ");
		if (null != returnVO) {
			countFromSql.append(" where ");
		}
		if ("" != returnVO.getSendPersonName()) {
			countFromSql.append(" send_personname = (SELECT del_name FROM bd_deliveryman WHERE id = '" + returnVO.getSendPersonName() + "')");
		}
		StringBuffer whereSql = this.getWhereSql(returnVO);
		Map<String, Object> map = new HashMap<String, Object>();
		if ("" != returnVO.getCreateTimeStr()) {
			countFromSql.append(" and send_time = '" + returnVO.getCreateTimeStr() + "'");
		}
		String countFromSqlStr = countFromSql.toString();
		if (countFromSqlStr.contains(" where  and ")) {
			countFromSqlStr = countFromSqlStr.replace(" where  and", " where ");
		}
		if (countFromSqlStr.endsWith("where ")) {
			countFromSqlStr = countFromSqlStr.substring(0, countFromSql.toString().length() - 6);
		}
		Query queryCount = this.getSession().createSQLQuery(countFromSqlStr);
		Integer count = Integer.parseInt(queryCount.uniqueResult().toString());

		map.put("count", count);

		String sql1 = "SELECT * FROM or_return";
		if (null != returnVO) {
			sql1 += " where ";
		}
		if ("" != returnVO.getSendPersonName()) {
			sql1 += " send_personname=(SELECT del_name FROM bd_deliveryman WHERE id = '" + returnVO.getSendPersonName() + "') ";
		}
		if ("" != returnVO.getCreateTimeStr()) {
			sql1 += " and send_time = '" + returnVO.getCreateTimeStr() + "'";
		}
		if (sql1.contains("where  and")) {
			sql1 = sql1.replace(" where  and ", " where ");
		}
		if (sql1.endsWith("where ")) {
			sql1 = sql1.substring(0, sql1.length() - 6);
		}
		List<Object[]> returnLis = this.getSession().createSQLQuery(sql1).setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list();
		// List<ReturnVO> returnList = this.getSession().createQuery(sql)
		// .setFirstResult((pageNum - 1) * pageSize)
		// .setMaxResults(pageSize).list();
		List<ReturnVO> returnList = new ArrayList<ReturnVO>();

		int length = returnLis.size();
		for (int i = 0; i < length; i++) {
			ReturnVO returnVOTemp = new ReturnVO();
			Object[] obj = returnLis.get(i);
			System.out.println(obj[1]);
			returnVOTemp.setId(obj[0].toString());
			returnVOTemp.setSendPersonName(obj[2].toString());
			returnVOTemp.setBillNo(obj[1].toString());
			returnVOTemp.setNumber(Integer.parseInt(obj[3].toString()));
			// returnVOTemp.setSalary((BigDecimal)obj[3]);
			returnVOTemp.setMoney((Float) obj[4]);
			returnVOTemp.setCreateTime((java.util.Date) obj[5]);
			returnVOTemp.setStatus(obj[6].toString());
			returnList.add(returnVOTemp);
		}

		map.put("list", returnList);
		// map.put("footer", listTotal);

		return map;
	}

	/*
	 * 查询归班表————派费结算页面
	 */
	public Map<String, Object> queryReturn11(final ReturnVO returnVO, final int pageNum, final int pageSize) {
		Map<String, Object> map = new HashMap<String, Object>();

		// StringBuffer allFromSql = new StringBuffer("from ReturnVO ");
		StringBuffer countFromSql = new StringBuffer("SELECT COUNT(id) as count FROM or_return ");
		if (null != returnVO) {
			countFromSql.append(" where");
		}
		if (!returnVO.getSendPersonName().equals("null")) {
			countFromSql.append(" send_personname in (SELECT del_name FROM bd_deliveryman WHERE id in " + returnVO.getSendPersonName() + ")");
		}
		StringBuffer whereSql = this.getWhereSql(returnVO);

		if ("" != returnVO.getCreateTimeStr()) {
			countFromSql.append(" and send_time >= '" + returnVO.getCreateTimeStr() + "'");
		}
		if ("" != returnVO.getEndTimeStr()) {
			countFromSql.append(" and send_time <= '" + returnVO.getEndTimeStr() + "'");
		}
		String countFromSqlStr = countFromSql.toString();
		if (countFromSql.toString().contains("where and")) {
			countFromSqlStr = countFromSql.toString().replace("where and", "where");
		}
		if (countFromSql.toString().endsWith("where")) {
			countFromSqlStr = countFromSqlStr.substring(0, countFromSqlStr.length() - 5);
		}
		Query queryCount = this.getSession().createSQLQuery(countFromSqlStr);
		Integer count = Integer.parseInt(queryCount.uniqueResult().toString());

		map.put("count", count);

		String sql1 = "SELECT * FROM or_return ";
		if (null != returnVO) {
			sql1 += "where";
		}
		if (!returnVO.getSendPersonName().equals("null")) {
			sql1 += " send_personname in (SELECT del_name FROM bd_deliveryman WHERE id in " + returnVO.getSendPersonName() + ")";
		}
		if ("" != returnVO.getCreateTimeStr()) {
			sql1 += " and send_time >= '" + returnVO.getCreateTimeStr() + "'";
		}
		if ("" != returnVO.getEndTimeStr()) {
			sql1 += " and send_time <= '" + returnVO.getEndTimeStr() + "'";
		}
		if (sql1.contains("where and")) {
			sql1 = sql1.replace("where and", "where");
		}
		if (sql1.endsWith("where")) {
			sql1 = sql1.substring(0, sql1.length() - 5);
		}
		List<Object[]> returnLis = this.getSession().createSQLQuery(sql1).setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list();

		List<ReturnVO> listTotal = new ArrayList<ReturnVO>();
		ReturnVO totalvo = new ReturnVO();
		int num = 0;
		float money = 0;
		// BigDecimal salary = new BigDecimal(0);
		float commissionNum = 0f;
		float salary1 = 0f;
		totalvo.setSendPersonName("汇总：");
		listTotal.add(totalvo);
		// 把查询出来的结果拼装到ReturnVO类型的List中
		List<ReturnVO> returnList = new ArrayList<ReturnVO>();
		// 获得业务参数——派费提成
		float commission = this.getCommission();
		int length = returnLis.size();
		for (int i = 0; i < length; i++) {
			ReturnVO returnVOTemp = new ReturnVO();
			Object[] obj = returnLis.get(i);
			returnVOTemp.setSendPersonName(obj[2].toString());
			returnVOTemp.setBillNo(obj[1].toString());
			returnVOTemp.setNumber(Integer.parseInt(obj[3].toString()));
			// returnVOTemp.setSalary((BigDecimal) obj[7]);
			returnVOTemp.setSalary(new BigDecimal(Integer.parseInt(obj[3].toString()) * commission));
			// BigDecimal salary = new BigDecimal((int)obj[3]*commission);
			returnVOTemp.setMoney((Float) obj[4]);
			returnVOTemp.setCreateTime((java.util.Date) obj[5]);
			returnVOTemp.setStatus(obj[6].toString());
			returnList.add(returnVOTemp);
			num += returnVOTemp.getNumber();
			money += returnVOTemp.getMoney();
			commissionNum = num * commission;
			// if (null != returnVOTemp.getSalary()) {
			// salary = returnVOTemp.getSalary().add(salary);
			// }
		}
		BigDecimal salary = new BigDecimal(commissionNum);
		totalvo.setNumber(num);
		totalvo.setSalary(salary);
		totalvo.setMoney(money);

		map.put("list", returnList);
		map.put("footer", listTotal);

		return map;
	}

	/*
	 * 查询业务参数————派费提成
	 */
	public float getCommission() {
		String sql = "SELECT bd_busi_param.value FROM bd_busi_param WHERE bd_busi_param.name='提成'";
		List list = this.getSession().createSQLQuery(sql).list();
		float commission = Float.parseFloat(list.get(0).toString());
		// int a = (int)list.get(0);
		return commission;
	}

	/*
	 * 查询归班单————用归班单号
	 */
	public Map<String, Object> queryReturnByBillNo(String billNo) {
		// String sql =
		// " SELECT * FROM or_return WHERE return_billno='"+billNo+"'";
		// List<ReturnVO> list = this.getSession().createSQLQuery(sql).list();
		// Map<String, Object> map = new HashMap<String, Object>();
		// map.put("list", list);
		// return map;
		// 使用hql方式操作数据库，可直接把返回的数据封装成对象，如果用原生的jdbc需自己封装成所需实体，否则前台无法解析出来数据

		String hql = "FROM ReturnVO WHERE billNo=:billNo";
		Query query = this.getSession().createQuery(hql);
		query.setParameter("billNo", billNo);
		List<ReturnVO> list = query.list();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("list", list);
		map.put("count", 1);
		return map;
	}

	/*
	 * 查询归班单————用归班单号
	 */
	public ReturnVO queryReturnById(String id) {
		String hql = "FROM ReturnVO WHERE id=:id";
		Query query = this.getSession().createQuery(hql);
		query.setParameter("id", id);
		return (ReturnVO) query.uniqueResult();
	}

	private StringBuffer getWhereSql(final ReturnVO returnVO) {
		StringBuffer whereSql = new StringBuffer("");
		if (null != returnVO) {
			whereSql.append("where");
		}
		if ("" != returnVO.getSendPersonName()) {
			whereSql.append(" and send_personname='").append(returnVO.getSendPersonName()).append("'");
		}
		if ("" != returnVO.getBillNo()) {
			whereSql.append(" and return_billno='").append(returnVO.getBillNo()).append("'");
		}
		// if ("" != returnVO.getCreateTimeStr()) {
		// whereSql.append(" and send_time ='")
		// .append(returnVO.getCreateTime()).append("'");
		// }
		// if ("" != returnVO.getEndTimeStr()) {
		// whereSql.append(" and send_time <=").append(returnVO.getEndTime())
		// .append("'");
		// }

		return whereSql;
	}

	private String processWhere(String sqlStr) {
		if (sqlStr.contains("where and")) {
			sqlStr = sqlStr.replace("where and", "where");
		} else if (sqlStr.contains("where")) {
			sqlStr = sqlStr.replace("where", "");
		}
		return sqlStr;
	}

	/**
	 *
	 * @Title: getReturnSettle
	 * @Description: 分页获取归班反馈信息
	 * @author 孟凡岭
	 * @param @param returnSettleDTO
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @return
	 * @return Map<String, Object> 返回类型
	 * @throws
	 */
	@SuppressWarnings("rawtypes")
	public Map<String, Object> getReturnSettle(ReturnSettleDTO returnSettleDTO, int pageNum, int pageSize) {
		StringBuffer sql = new StringBuffer("select DATE_FORMAT(o.complete_Time,'%Y-%m-%d') as 'returnDate' ");
		sql.append(",sum(o.Order_Total_Price) as 'totalPrice' ");
		sql.append(",sum(o.Take_Meal_Price) as 'takeMealPrice',SUM(o.Delivery_Price) as 'deliveryPrice' ");
		sql.append(",sum(o.Order_Total_Price)-sum(o.Take_Meal_Price) as 'upMoney' ");
		sql.append(",count(*) as 'number',man.id as 'deliveryId',man.del_name as 'deliveryName' from or_order o ");
		sql.append("  INNER JOIN bd_deliveryman man on man.id=o.deliveryman_id  where 1=1 and o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + " and pay_method=2 ");
		sql.append(" and returnRecord_Id is null ");
		/**
		 * 存放条件参数和条件参数类型
		 */
		Object[] param = new Object[1];
		int[] types = new int[1];
		if (StringUtils.isNotBlank(returnSettleDTO.getDeliveryManId())) {
			sql.append(" and man.id=? ");
			param[0] = returnSettleDTO.getDeliveryManId();
			types[0] = Types.VARCHAR;
		}
		sql.append(" and DATE_FORMAT(o.complete_Time,'%Y-%m-%d') between '" + returnSettleDTO.getStartTime() + "'");
		sql.append(" and '" + returnSettleDTO.getEndTime() + "'");
		sql.append(" GROUP BY o.deliveryman_id,DATE_FORMAT(o.complete_time,'%Y-%m-%d') ");
		StringBuffer countSql = new StringBuffer("select count(*) from (");
		countSql.append(sql + ") as total ");
		sql.append(" limit " + ((pageNum - 1) * pageSize) + "," + pageSize);
		Map<String, Object> map = new HashMap<String, Object>();
		int count = 0;
		/**
		 * 如果有条件参数，使用参数查询，否则会报异常
		 */
		;
		if (param[0] != null) {
			count = this.getJdbcTemplate().queryForInt(countSql.toString(), param, types);
			map.put("list", this.getJdbcTemplate().queryForList(sql.toString(), param, types));
		} else {
			count = this.getJdbcTemplate().queryForInt(countSql.toString());
			map.put("list", this.getJdbcTemplate().queryForList(sql.toString()));
		}
		map.put("count", count);
		return map;
	}

	/**
	 * @param settleMap
	 *
	 * @Title: upMoney
	 * @Description: 配送员交款
	 * @author 孟凡岭
	 * @param @param deliveryIds
	 * @param @param returnDate
	 * @return void 返回类型
	 * @throws
	 */
	public List<ReturnRecordVO> upMoney(final String id, final String date) throws Exception {
		// TODO Auto-generated method stub
		TSUser user = ResourceUtil.getSessionUserName();
		List<ReturnRecordVO> recordList = new ArrayList<ReturnRecordVO>();
		StringBuffer sql = new StringBuffer();

		final List<Map<String, Object>> list = this.getReturnSettle(id, date);
		if (list.size() > 0) {
			Date sysDate = this.getSystemDate();
			SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			for (int i = 0; i < list.size(); i++) {
				ReturnRecordVO record = new ReturnRecordVO();
				Map<String, Object> map = list.get(i);
				record.setDeliveryManId(map.get("deliveryId").toString());
				if (map.get("deliveryPrice") != null) {
					record.setDeliveryMoney(map.get("deliveryPrice").toString());
				}
				if (map.get("takeMealPrice") != null) {
					record.setTakeMoney(map.get("takeMealPrice").toString());
				}
				if (map.get("totalPrice") != null) {
					record.setTotalMoney(map.get("totalPrice").toString());
				}
				if (map.get("completeTime") != null) {
					record.setOrderDate(sd.parse(map.get("completeTime").toString()));
				}
				if (map.get("upMoney") != null) {
					record.setUpMoney(map.get("upMoney").toString());
				}
				record.setNumber(Integer.valueOf(map.get("number").toString()));
				record.setSettleDate(sysDate);
				record.setOperUserId(user.getId());
				recordList.add(record);
			}
		}
		return recordList;

	}

	/**
	 *
	 * @Title: getReturnSettle
	 * @Description: 在交款之前执行一次查询，获取配送员ID
	 * @author 孟凡岭
	 * @param @param deliveryIds
	 * @param @param returnDate
	 * @param @return
	 * @return List<String> 返回类型
	 * @throws
	 */
	public List<String> getDeliveryManReturnSettle(List<String> deliveryIds, List<String> returnDate) {
		int listSize = deliveryIds.size();
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < listSize; i++) {
			list.add("'" + deliveryIds.get(i) + "'");
		}
		String ids = StringUtils.join(list.toArray(), ",");
		list.clear();
		for (int i = 0; i < returnDate.size(); i++) {
			list.add("'" + returnDate.get(i) + "'");
		}
		String dates = StringUtils.join(list.toArray(), ",");
		StringBuffer sql = new StringBuffer("select DISTINCT o.deliveryman_id from or_order o where o.status=5  ");
		sql.append(" and date_format(complete_Time,'%Y-%m-%d') in (" + dates + ") ");
		sql.append(" and o.deliveryman_id in(" + ids + ")");
		return this.getJdbcTemplate().queryForList(sql.toString(), String.class);
	}

	/**
	 *
	 * @Title: getReturnSettle
	 * @Description: 获取指定配送员的结算信息
	 * @author 孟凡岭
	 * @param @param deliveryIds
	 * @param @param returnDate
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> getReturnSettle(String deliveryIds, String returnDate) {
		StringBuffer sql = new StringBuffer("select DATE_FORMAT(o.complete_Time,'%Y-%m-%d %H:%m') as 'completeTime' ");
		sql.append(",sum(o.Order_Total_Price) as 'totalPrice' ");
		sql.append(",sum(o.Take_Meal_Price) as 'takeMealPrice',SUM(o.Delivery_Price) as 'deliveryPrice' ");
		sql.append(",sum(o.Order_Total_Price)-sum(o.Take_Meal_Price) as 'upMoney' ");
		sql.append(",count(*) as 'number',man.id as 'deliveryId',man.del_name as 'deliveryName' from or_order o ");
		sql.append("  INNER JOIN bd_deliveryman man on man.id=o.deliveryman_id and man.id='" + deliveryIds + "' ");
		sql.append("  where 1=1 and o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + " and pay_method=2 ");
		sql.append(" and DATE_FORMAT(o.complete_Time,'%Y-%m-%d')='" + returnDate + "' and returnRecord_Id is null");
		// sql.append(" GROUP BY	o.deliveryman_id,o.complete_Time ");
		return this.getJdbcTemplate().queryForList(sql.toString());
	}

	/**
	 * @param returnDate
	 *
	 * @Title: updateOrderReturn
	 * @Description: 更新订单的归班结算
	 * @author 孟凡岭
	 * @param @param recordList
	 * @return void 返回类型
	 * @throws
	 */
	public void updateOrderReturn(final List<ReturnRecordVO> recordList, final List<String> returnDate) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("UPDATE or_order set returnRecord_Id=? ");
		sql.append(" where DATE_FORMAT(complete_Time,'%Y-%m-%d')=? and status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + " and deliveryman_id=? ");
		sql.append(" and returnRecord_Id is null and pay_method=2 ");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ReturnRecordVO r = recordList.get(i);
				ps.setString(1, r.getId());
				ps.setString(2, returnDate.get(i));
				ps.setString(3, r.getDeliveryManId());
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return recordList.size();
			}
		});
	}

	/**
	 * @throws ParseException
	 *
	 * @Title: settleMoney
	 * @Description: 结算派费
	 * @author 孟凡岭
	 * @param @param string
	 * @param @param string2
	 * @param @return
	 * @return List<ReturnRecordVO> 返回类型
	 * @throws
	 */
	public List<SettleAccoutsRecordVO> settleMoney(final String id, final String date) throws Exception {
		TSUser user = ResourceUtil.getSessionUserName();
		List<SettleAccoutsRecordVO> recordList = new ArrayList<SettleAccoutsRecordVO>();
		final List<Map<String, Object>> list = this.getSettleDelivery(id, date);
		if (list.size() > 0) {
			StringBuffer sql = new StringBuffer();
			sql.append("update or_order set is_Settle_Delivery=1 ");
			sql.append(" where deliveryman_id=? and is_Settle_Delivery=0 and status=" + OrderStatusEnum.SIGN_RECEIVE.getValue());
			sql.append(" and date_format(complete_Time,'%Y-%m-%d')=? and pay_method=2 ");
			this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					// TODO Auto-generated method stub
					ps.setString(1, id);
					ps.setString(2, date);
				}

				@Override
				public int getBatchSize() {
					// TODO Auto-generated method stub
					return list.size();
				}
			});
			Date sysDate = this.getSystemDate();
			SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			for (int i = 0; i < list.size(); i++) {
				SettleAccoutsRecordVO record = new SettleAccoutsRecordVO();
				Map<String, Object> map = list.get(i);
				record.setDeliveryManId(map.get("deliveryId").toString());
				record.setDeliveryMoney(map.get("deliveryPrice").toString());
				record.setNumber(Integer.valueOf(map.get("number").toString()));
				record.setOrderDate(sd.parse(map.get("completeTime").toString()));
				record.setSettleDate(sysDate);
				record.setOperUserId(user.getId());
				recordList.add(record);
			}
		}
		return recordList;
	}

	/**
	 *
	 * @Title: getSettleDelivery
	 * @Description: 获取指定配送员的运费
	 * @author 孟凡岭
	 * @param @param id
	 * @param @param date
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> getSettleDelivery(String id, String date) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("select o.deliveryman_id as 'deliveryId',  DATE_FORMAT(o.complete_Time,'%Y-%m-%d %H:%m') as 'completeTime'");
		sql.append(",SUM(o.Delivery_Price) as 'deliveryPrice' ");
		sql.append(",count(*) as 'number' from or_order o where 1=1 and pay_method=2 ");
		sql.append(" and o.deliveryman_id='" + id + "' and (o.status=5 or o.status=10) and o.is_Settle_Delivery=0");
		sql.append(" and DATE_FORMAT(o.complete_Time,'%Y-%m-%d')='" + date + "' GROUP BY o.deliveryman_id,DATE_FORMAT(o.complete_Time,'%Y-%m-%d')");
		return this.getJdbcTemplate().queryForList(sql.toString());
	}

	/**
	 *
	 * @Title: updateSettleRecord
	 * @Description: 更新结算记录
	 * @author 孟凡岭
	 * @param @param recordList
	 * @param @param returnDate
	 * @return void 返回类型
	 * @throws
	 */
	public void updateSettleRecord(final List<SettleAccoutsRecordVO> recordList, final List<String> returnDate) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("UPDATE or_order  set settleRecord_Id=? ");
		sql.append(" where DATE_FORMAT(complete_Time,'%Y-%m-%d')=? and status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + " and deliveryman_id=? ");
		sql.append(" and is_Settle_Delivery=1 and pay_method=2 and  settleRecord_Id is null ");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				SettleAccoutsRecordVO r = recordList.get(i);
				ps.setString(1, r.getId());
				ps.setString(2, returnDate.get(i));
				ps.setString(3, r.getDeliveryManId());
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return recordList.size();
			}
		});
	}
}
